package com.tdcy.framework.dao.metadata;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

import com.tdcy.framework.exception.BaseException;
import com.tdcy.framework.util.StringUtils;

/**
 * The Class TableMetadata.
 */
@SuppressWarnings(value={"unchecked", "rawtypes"})
public class TableMetadata {

	/** The catalog. */
	private final String catalog;

	/** The schema. */
	private final String schema;

	/** The name. */
	private final String name;

	/** The columns. */
	private final Map<String, ColumnMetadata> columns = new LinkedHashMap<String, ColumnMetadata>();

	/** The cols. */
	private final List<String> cols = new ArrayList<String>();

	/** The keys. */
	private final List<String> keys = new ArrayList<String>();

	/** The defaults. */
	private final Map<String, String> defaults = new HashMap<String, String>();

	/** The select string. */
	private final String queryCols;

	/**
	 * Instantiates a new table meta data.
	 * 
	 * @param rs
	 *            the rs
	 * @param meta
	 *            the meta
	 * @throws SQLException
	 *             the sQL exception
	 */
	TableMetadata(final ResultSet rs, final DatabaseMetaData meta) throws SQLException {
		catalog = rs.getString("TABLE_CAT");
		schema = rs.getString("TABLE_SCHEM");
		name = rs.getString("TABLE_NAME");

		initColumns(meta);
		initPrimaryKeys(meta);

		queryCols = StringUtils.toDelimitedString(this.cols, ",").toUpperCase();
	}

	/**
	 * Gets the name.
	 * 
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#toString()
	 */
	@Override
	public String toString() {
		return "TableMetadata(" + name + ')';
	}

	/**
	 * Gets the column meta data.
	 * 
	 * @param columnName
	 *            the column name
	 * @return the column meta data
	 */
	public ColumnMetadata getColumnMetaData(final String columnName) {
		return columns.get(columnName.toLowerCase());
	}

	/**
	 * Gets the columns.
	 * 
	 * @return the columns
	 */
	public List<String> getCols() {
		return Collections.unmodifiableList(this.cols);
	}

	/**
	 * Gets the keys.
	 * 
	 * @return the keys
	 */
	public List<String> getKeys() {
		return Collections.unmodifiableList(this.keys);
	}

	/**
	 * Gets the defaults.
	 * 
	 * @return the defaults
	 */
	public Map<String, String> getDefaults() {
		return Collections.unmodifiableMap(this.defaults);
	}

	/**
	 * Adds the column.
	 * 
	 * @param rs
	 *            the rs
	 * @throws SQLException
	 *             the sQL exception
	 */
	private void addColumn(final ResultSet rs) throws SQLException {
		String column = rs.getString("COLUMN_NAME");

		if (column == null)
			return;

		if (getColumnMetaData(column) == null) {
			ColumnMetadata info = new ColumnMetadata(rs);
			String columnName = column.toLowerCase();
			columns.put(columnName, info);
			cols.add(columnName);

			if (StringUtils.hasLength(info.getDefaultValue())) {
				defaults.put(columnName, info.getDefaultValue());
			}
		}
	}

	/**
	 * Inits the primary keys.
	 * 
	 * @param meta
	 *            the meta
	 * @throws SQLException
	 *             the sQL exception
	 */
	private void initPrimaryKeys(final DatabaseMetaData meta) throws SQLException {
		ResultSet rs = null;

		try {
			if (meta.storesUpperCaseIdentifiers()) {
				rs = meta.getPrimaryKeys(StringUtils.toUpperCase(catalog), StringUtils.toUpperCase(schema), StringUtils.toUpperCase(name));
			} else if (meta.storesLowerCaseIdentifiers()) {
				rs = meta.getPrimaryKeys(StringUtils.toLowerCase(catalog), StringUtils.toLowerCase(schema), StringUtils.toLowerCase(name));
			} else {
				rs = meta.getPrimaryKeys(catalog, schema, name);
			}

			while (rs.next()) {
				this.keys.add(rs.getString("COLUMN_NAME").toLowerCase());
			}
		} finally {
			if (rs != null)
				rs.close();
		}
	}

	/**
	 * Inits the columns.
	 * 
	 * @param meta
	 *            the meta
	 * @throws SQLException
	 *             the sQL exception
	 */
	private void initColumns(final DatabaseMetaData meta) throws SQLException {
		ResultSet rs = null;

		try {
			if (meta.storesUpperCaseIdentifiers()) {
				rs = meta.getColumns(StringUtils.toUpperCase(catalog), StringUtils.toUpperCase(schema), StringUtils.toUpperCase(name), "%");
			} else if (meta.storesLowerCaseIdentifiers()) {
				rs = meta.getColumns(StringUtils.toLowerCase(catalog), StringUtils.toLowerCase(schema), StringUtils.toLowerCase(name), "%");
			} else {
				rs = meta.getColumns(catalog, schema, name, "%");
			}

			while (rs.next())
				addColumn(rs);
		} finally {
			if (rs != null)
				rs.close();
		}
	}

	/**
	 * Match in parameter value.
	 * 
	 * @param column
	 *            the column
	 * @param parameterSource
	 *            the parameter source
	 * @param caseInsensitiveParameterNames
	 *            the case insensitive parameter names
	 * @return the object
	 */
	private Pair<String, Object> matchInParameterValue(final String column, final SqlParameterSource parameterSource, final Map caseInsensitiveParameterNames) {
		if (parameterSource.hasValue(column)) {
			return new Pair(column, parameterSource.getValue(column));
		}

		if (caseInsensitiveParameterNames.containsKey(column)) {
			String name = (String) caseInsensitiveParameterNames.get(column);
			return new Pair(name, parameterSource.getValue(name));
		}

		return null;
	}

	/**
	 * Builds the batch values.
	 * 
	 * @param coll
	 *            the coll
	 * @param names
	 *            the names
	 * @param firstValue
	 *            the first value
	 * @return the object[][]
	 */
	private List<List<Object>> buildBatchValues(final Collection coll, final List<String> names, final List firstValue) {
		Iterator<Object> it = coll.iterator();
		List batch = new ArrayList(coll.size());
		if (firstValue != null && it.hasNext()) {
			batch.add(firstValue);
			it.next();
		}

		Object value = null;
		SqlParameterSource sqlParameterSource = null;
		while (it.hasNext()) {
			value = it.next();
			if (value instanceof Map) {
				sqlParameterSource = new MapSqlParameterSource((Map) value);
			}

			List<Object> values = new ArrayList<Object>();
			for (String name : names) {
				values.add(sqlParameterSource.getValue(name));
			}

			batch.add(values);
		}

		return batch;
	}

	/**
	 * 创建insert语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param defaultMap
	 *            缺省值
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createInsertSql(final Object paramValue, final Map<String, Object> defaultMap) throws BaseException {
		return createInsertSql(paramValue, defaultMap, false);
	}

	/**
	 * 创建insert语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param defaultMap
	 *            缺省值
	 * @param notNull
	 *            非空字段标志
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createInsertSql(final Object paramValue, final Map<String, Object> defaultMap, final boolean notNull) throws BaseException {
		if (paramValue == null) {
			return null;
		}

		Collection coll = null;
		Object param = null;
		int paramSize = 0;
		if (paramValue instanceof Collection) {
			coll = (Collection) paramValue;
			if (StringUtils.isEmpty(coll)) {
				return null;
			}
			param = coll.iterator().next();
			paramSize = coll.size();
		} else {
			param = paramValue;
			paramSize = 1;
		}

		SqlParameterSource parameterSource = null;
		if (param instanceof Map) {
			parameterSource = new MapSqlParameterSource((Map) param);
		}
		Map caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

		StringBuilder insertStatement = new StringBuilder();
		StringBuilder valuesStatement = new StringBuilder();

		Object value = null;
		String split = null;
		Set<String> defaults = defaultMap == null ? Collections.EMPTY_SET : defaultMap.keySet();
		StringBuilder nullStatement = new StringBuilder();

		List<String> names = new ArrayList<String>();
		List<Object> params = new ArrayList<Object>();
		Pair<String, Object> pair = null;
		for (String columnName : this.cols) {
			// 处理缺省值
			if (defaults.contains(columnName)) {
				if (split != null) {
					insertStatement.append(split);
					valuesStatement.append(split);
				}

				value = defaultMap.get(columnName);

				valuesStatement.append(value == null ? "null" : value.toString());

				insertStatement.append(columnName.toUpperCase());
				split = ", ";
			} else {
				pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);

				if (pair == null || (notNull && pair.getSecond() == null)) {
					if (nullStatement.length() > 0) {
						nullStatement.append(", ");
					}
					nullStatement.append(columnName.toUpperCase());
				} else {
					if (split != null) {
						insertStatement.append(split);
						valuesStatement.append(split);
					}

					insertStatement.append(columnName.toUpperCase());
					valuesStatement.append("?");

					names.add(pair.getFirst());
					params.add(pair.getSecond());

					split = ", ";
				}
			}
		}

		if (nullStatement.length() > 0) {
			insertStatement.append("/*");
			insertStatement.append(nullStatement.toString());
			insertStatement.append("*/");
		}

		String sql = String.format("INSERT INTO %1$s (%2$s) VALUES(%3$s)", this.name, insertStatement.toString(), valuesStatement.toString());

		Map sqlMap = new HashMap();
		sqlMap.put("sql", sql);
		List batch = null;
		if (paramSize == 1) {
			batch = new ArrayList();
			batch.add(params);
		} else {
			batch = buildBatchValues(coll, names, params);
		}

		sqlMap.put("params", StringUtils.toArrayOfArray(batch));
		return sqlMap;
	}

	/**
	 * 创建update语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param defaultMap
	 *            缺省值
	 * @param updates
	 *            更新列名集合
	 * @param wheres
	 *            更新条件集合
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createUpdateSql(final Object paramValue, final Map<String, Object> defaultMap, final String[] updates, final String[] wheres) throws BaseException {
		return createUpdateSql(paramValue, defaultMap, false, updates, wheres);
	}

	/**
	 * 创建update语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param defaultMap
	 *            缺省值
	 * @param notNull
	 *            非空标志
	 * @param updates
	 *            更新列名集合
	 * @param wheres
	 *            更新条件集合
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createUpdateSql(final Object paramValue, final Map<String, Object> defaultMap, final boolean notNull, final String[] updates, final String[] wheres) throws BaseException {
		if (paramValue == null) {
			return null;
		}

		Collection coll = null;
		Object param = null;
		int paramSize = 0;
		if (paramValue instanceof Collection) {
			coll = (Collection) paramValue;
			if (StringUtils.isEmpty(coll)) {
				return null;
			}
			param = coll.iterator().next();
			paramSize = coll.size();
		} else {
			param = paramValue;
			paramSize = 1;
		}

		// 条件集合
		Set<String> whereSet = new HashSet<String>();
		if (StringUtils.isEmpty(wheres)) {
			whereSet.addAll(this.keys);
		} else {
			for (String key : wheres) {
				whereSet.add(key.toLowerCase());
			}
		}
		if (StringUtils.isEmpty(whereSet)) {
			throw new BaseException("更新" + this.name + "表的数据，条件列名不能为空");
		}

		// 更新集合
		Set<String> updateSet = new HashSet<String>();

		SqlParameterSource parameterSource = null;
		if (param instanceof Map) {
			parameterSource = new MapSqlParameterSource((Map) param);
			if (StringUtils.isEmpty(updates)) {
				for (Object key : ((Map) param).keySet()) {
					updateSet.add(key.toString().toLowerCase());
				}
			} else {
				for (String key : updates) {
					updateSet.add(key.toLowerCase());
				}
			}
		}

		Map caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

		StringBuilder updateStatement = new StringBuilder();
		StringBuilder whereStatement = new StringBuilder();

		Object value = null;

		String split = null;
		Set<String> defaults = defaultMap == null ? Collections.EMPTY_SET : defaultMap.keySet();

		List<String> names = new ArrayList<String>();
		List<Object> params = new ArrayList<Object>();
		Pair<String, Object> pair = null;
		for (String columnName : updateSet) {
			if (!this.cols.contains(columnName) || whereSet.contains(columnName)) {
				continue;
			}

			// 处理缺省值
			if (defaults.contains(columnName)) {
				if (split != null) {
					updateStatement.append(split);
				}

				value = defaultMap.get(columnName);

				updateStatement.append(columnName.toUpperCase());
				updateStatement.append(" = ");
				updateStatement.append(value == null ? "null" : value.toString());

				split = ", ";
			} else {
				pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);
				if (pair != null) {
					if (notNull && pair.getSecond() == null) {// 忽略空字段
						continue;
					}

					if (split != null) {
						updateStatement.append(split);
					}

					updateStatement.append(columnName.toUpperCase());
					updateStatement.append(" = ?");

					names.add(pair.getFirst());
					params.add(pair.getSecond());

					split = ", ";
				}
			}
		}

		if (updateStatement.length() == 0) {
			throw new BaseException("更新" + this.name + "表的数据，不存在有效的更新列");
		}

		for (String columnName : whereSet) {
			pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);

			if (pair == null) {
				throw new BaseException("更新" + this.name + "表的数据，条件列名" + columnName + "不存在");
			}

			if (whereStatement.length() > 0) {
				whereStatement.append(" AND ");
			}
			whereStatement.append(columnName.toUpperCase());
			whereStatement.append(" = ?");

			names.add(pair.getFirst());
			params.add(pair.getSecond());
		}

		if (whereStatement.length() == 0) {
			throw new BaseException("更新" + this.name + "表的数据，不存在有效的条件列");
		}

		String sql = String.format("UPDATE %1$s SET %2$s WHERE %3$s", this.name, updateStatement.toString(), whereStatement.toString());

		Map sqlMap = new HashMap();
		sqlMap.put("sql", sql);
		List batch = null;
		if (paramSize == 1) {
			batch = new ArrayList();
			batch.add(params);
		} else {
			batch = buildBatchValues(coll, names, params);
		}

		sqlMap.put("params", StringUtils.toArrayOfArray(batch));
		return sqlMap;
	}

	/**
	 * 创建delete语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param wheres
	 *            条件列名集合
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createDeleteSql(final Object paramValue, final String[] wheres) throws BaseException {
		if (paramValue == null) {
			return null;
		}

		Set<String> whereSet = new HashSet<String>();
		if (StringUtils.isEmpty(wheres)) {
			whereSet.addAll(this.keys);
		} else {
			for (String key : wheres) {
				whereSet.add(key.toLowerCase());
			}
		}

		if (StringUtils.isEmpty(whereSet)) {
			throw new BaseException("删除" + this.name + "表的数据，条件列名不能为空");
		}

		Collection coll = null;
		Object param = null;
		int paramSize = 0;
		if (paramValue instanceof Collection) {
			coll = (Collection) paramValue;
			if (StringUtils.isEmpty(coll)) {
				return null;
			}
			param = coll.iterator().next();
			paramSize = coll.size();
		} else {
			param = paramValue;
			paramSize = 1;
		}

		SqlParameterSource parameterSource = null;
		if (param instanceof Map) {
			parameterSource = new MapSqlParameterSource((Map) param);
		}
		Map caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

		StringBuilder deleteStatement = new StringBuilder();

		List<String> names = new ArrayList<String>();
		List<Object> params = new ArrayList<Object>();
		Pair<String, Object> pair = null;
		for (String columnName : whereSet) {
			pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);

			if (pair == null) {
				throw new BaseException("删除" + this.name + "表的数据，条件列名" + columnName + "不存在");
			}

			if (deleteStatement.length() > 0) {
				deleteStatement.append(" AND ");
			}
			deleteStatement.append(columnName.toUpperCase());
			deleteStatement.append(" = ?");

			names.add(pair.getFirst());
			params.add(pair.getSecond());
		}

		String sql = String.format("DELETE FROM %1$s WHERE %2$s", this.name, deleteStatement.toString());

		Map sqlMap = new HashMap();
		sqlMap.put("sql", sql);
		List batch = null;
		if (paramSize == 1) {
			batch = new ArrayList();
			batch.add(params);
		} else {
			batch = buildBatchValues(coll, names, params);
		}

		sqlMap.put("params", StringUtils.toArrayOfArray(batch));
		return sqlMap;
	}

	/**
	 * 创建count语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param wheres
	 *            条件列名集合
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createCountSql(final Object paramValue, final String[] wheres) throws BaseException {
		if (paramValue == null) {
			return null;
		}

		Set<String> whereSet = new HashSet<String>();
		if (StringUtils.isEmpty(wheres)) {
			whereSet.addAll(this.keys);
		} else {
			for (String key : wheres) {
				whereSet.add(key.toLowerCase());
			}
		}

		if (StringUtils.isEmpty(whereSet)) {
			throw new BaseException("计数" + this.name + "表的数据，条件列名不能为空");
		}

		StringBuilder countStatement = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		SqlParameterSource parameterSource = null;
		if (paramValue instanceof Map) {
			parameterSource = new MapSqlParameterSource((Map) paramValue);
		}
		Map caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

		Pair<String, Object> pair = null;
		for (String columnName : whereSet) {
			if (countStatement.length() > 0) {
				countStatement.append(" AND ");
			}

			pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);

			if (pair == null) {
				throw new BaseException("计数" + this.name + "表的数据，条件列名" + columnName + "不存在");
			}
			countStatement.append(columnName.toUpperCase());
			countStatement.append(" = ?");

			params.add(pair.getSecond());
		}

		String sql = String.format("SELECT COUNT(1) FROM %1$s WHERE %2$s", this.name, countStatement.toString());

		Map sqlMap = new HashMap();
		sqlMap.put("sql", sql);
		sqlMap.put("params", StringUtils.toArray(params));
		return sqlMap;
	}

	/**
	 * 创建query语句.
	 * 
	 * @param paramValue
	 *            参数值
	 * @param wheres
	 *            条件列名集合
	 * @return map对象(sql和params)
	 * @throws BaseException
	 *             the exception
	 */
	public Map<String, Object> createQuerySql(final Object paramValue, final String[] wheres) throws BaseException {
		if (paramValue == null) {
			return null;
		}

		Set<String> whereSet = new HashSet<String>();
		if (StringUtils.isEmpty(wheres)) {
			whereSet.addAll(this.keys);
		} else {
			for (String key : wheres) {
				whereSet.add(key.toLowerCase());
			}
		}

		if (StringUtils.isEmpty(whereSet)) {
			throw new BaseException("查询" + this.name + "表的数据，条件列名不能为空");
		}

		StringBuilder countStatement = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		SqlParameterSource parameterSource = null;
		if (paramValue instanceof Map) {
			parameterSource = new MapSqlParameterSource((Map) paramValue);
		}
		Map caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);

		Pair<String, Object> pair = null;
		for (String columnName : whereSet) {
			if (countStatement.length() > 0) {
				countStatement.append(" AND ");
			}

			pair = matchInParameterValue(columnName, parameterSource, caseInsensitiveParameterNames);

			if (pair == null) {
				throw new BaseException("查询" + this.name + "表的数据，条件列名" + columnName + "不存在");
			}
			countStatement.append(columnName.toUpperCase());
			countStatement.append(" = ?");

			params.add(pair.getSecond());
		}

		String sql = String.format("SELECT %1$s FROM %2$s WHERE %3$s", this.queryCols, this.name, countStatement.toString());

		Map sqlMap = new HashMap();
		sqlMap.put("sql", sql);
		sqlMap.put("params", StringUtils.toArray(params));
		return sqlMap;
	}
}